Cross reference table example: More than one input and output value
A cross reference table can be used to accept more than one input value and produce more than one output value. The example here shows a cross reference table lookup within a function.
In addition to setting up the function, you need to map all input elements from the source profile to the input values in the function. You also need to map all output values from the function to the output elements in the destination profile.
For example, when referring to the U.S. states:
- System A uses the State Name value and the Other Code value
| System A Field Name | Example Input Value | System B Field Name | SQL SELECT Statement | Example Output Value | Explanation |
|---|---|---|---|---|---|
| State_Name | Alabama | FIPS_Alpha_Code | SELECT FIPS_Alpha_Code FROM State_Cross-Reference_Example WHERE State_Name = Input_Element | AL | When the input State Name in System A is "Alabama", the query returns "AL" as the FIPS Alpha Code in System B. |
- System B uses the FIPS Alpha Code value and the FIPS Numeric Code value
| System A Field Name | Example Input Value | System B Field Name | SQL SELECT Statement | Example Output Value | Explanation |
|---|---|---|---|---|---|
| Other_Code | a | FIPS_Numeric_Code | SELECT FIPS_Numeric_Code FROM State_Cross-Reference_Example WHERE Other_Code = Input_Element | 01 | When the input Other Code in System A is "a", the query returns "01" as the FIPS Numeric Code in System B. |
